﻿Imports System.Data.SqlClient
Imports Control_Vuelos_BE

Public Class ConsolidadoADO
    ReadOnly MiConexion As New ConexionADO()
    ReadOnly cnx As New SqlConnection(MiConexion.GetCnx)
    ReadOnly cmd As New SqlCommand()

    Public Function pr_ActualizarProvision(ByVal pProvision As V_ProvisionBE) As Boolean
        Dim lFlgNuevo As Boolean = True
        Dim oTran As SqlTransaction
        Using cn As New SqlConnection(MiConexion.GetCnx)
            Dim oTranCtb As SqlTransaction
            Using cnctb As New SqlConnection(ConexionADO.GetCnxContabilidad)
                Try
                    cn.Open()
                    oTran = cn.BeginTransaction(IsolationLevel.Serializable)
                    ' Actualizando la provision
                    Dim cmdBusCabPro As New SqlCommand("SELECT COUNT(*) FROM dbo.V_Provision WHERE (NroTicket=@NroTicket)", cn)
                    Using cmdBusCabPro
                        cmdBusCabPro.CommandType = CommandType.Text
                        cmdBusCabPro.Parameters.Add("@NroTicket", SqlDbType.Char, 8)
                        cmdBusCabPro.Parameters("@NroTicket").Value = pProvision.NroTicket.Trim
                        cmdBusCabPro.Transaction = oTran
                        Dim nReg As Integer = CInt(cmdBusCabPro.ExecuteScalar())
                        ' creo el registro del ticket
                        Dim sInstr As String = "INSERT INTO dbo.V_Provision(NroTicket, NroPeriodo, CodAsiento, NroAsiento, TipDocumento, NroDocumento, FchIngreso, FchEmision, FchVuelo, MonTipoCambio, CodMoneda, MonTotal, CodEmpresa, NroConPago, FlgDetraccion) " & "VALUES (@NroTicket, @NroPeriodo, @CodAsiento, @NroAsiento, @TipDocumento, @NroDocumento, @FchIngreso, @FchEmision, @FchVuelo, @MonTipoCambio, @CodMoneda, @MonTotal, @CodEmpresa, @NroConPago, @FlgDetraccion)"
                        If nReg > 0 Then
                            'Si existe el ticket, actualizo el registro del ticket
                            sInstr = "UPDATE dbo.V_Provision SET NroPeriodo=@NroPeriodo, CodAsiento=@CodAsiento, NroAsiento=@NroAsiento, TipDocumento=@TipDocumento, NroDocumento=@NroDocumento, FchIngreso=@FchIngreso, FchEmision=@FchEmision, FchVuelo=@FchVuelo, MonTipoCambio=@MonTipoCambio, CodMoneda=@CodMoneda, MonTotal=@MonTotal, CodEmpresa=@CodEmpresa, NroConPago=@NroConPago, FlgDetraccion=@FlgDetraccion where NroTicket=@NroTicket"
                        End If
                        Dim cmdActCabPro As New SqlCommand(sInstr, cn)
                        Using cmdActCabPro
                            cmdActCabPro.Parameters.AddWithValue("@NroTicket", pProvision.NroTicket)
                            cmdActCabPro.Parameters.AddWithValue("@NroPeriodo", pProvision.NroPeriodo)
                            cmdActCabPro.Parameters.AddWithValue("@CodAsiento", pProvision.CodAsiento)
                            cmdActCabPro.Parameters.AddWithValue("@NroAsiento", pProvision.NroAsiento.Trim)
                            cmdActCabPro.Parameters.AddWithValue("@TipDocumento", pProvision.TipDocumento)
                            cmdActCabPro.Parameters.AddWithValue("@NroDocumento", pProvision.NroDocumento)
                            cmdActCabPro.Parameters.AddWithValue("@FchIngreso", pProvision.FchIngreso)
                            cmdActCabPro.Parameters.AddWithValue("@FchEmision", pProvision.FchEmision)
                            cmdActCabPro.Parameters.AddWithValue("@FchVuelo", pProvision.FchVuelo)
                            cmdActCabPro.Parameters.AddWithValue("@MonTipoCambio", pProvision.MonTipoCambio)
                            cmdActCabPro.Parameters.AddWithValue("@CodMoneda", pProvision.CodMoneda)
                            cmdActCabPro.Parameters.AddWithValue("@MonTotal", pProvision.MonTotal)
                            cmdActCabPro.Parameters.AddWithValue("@CodEmpresa", pProvision.CodEmpresa)
                            cmdActCabPro.Parameters.AddWithValue("@NroConPago", pProvision.NroConPago)
                            cmdActCabPro.Parameters.AddWithValue("@FlgDetraccion", pProvision.FlgDetraccion)
                            cmdActCabPro.Transaction = oTran
                            cmdActCabPro.ExecuteNonQuery()
                        End Using
                    End Using
                    oTran.Commit()
                    cn.Close()
                    ' Actualizando la provision en contabilidad
                    cnctb.Open()
                    oTranCtb = cnctb.BeginTransaction(IsolationLevel.Serializable)
                    Dim cmdBusProCon As New SqlCommand("SELECT COUNT(*) FROM dbo.CabeceraProvision WHERE (CodUbi='L') AND (tiptic='P') AND (nrotic=@NroTicket)", cnctb)
                    Using cmdBusProCon
                        cmdBusProCon.CommandType = CommandType.Text
                        cmdBusProCon.Parameters.Add("@NroTicket", SqlDbType.Char, 8)
                        cmdBusProCon.Parameters("@NroTicket").Value = pProvision.NroTicket.Trim
                        cmdBusProCon.Transaction = oTranCtb
                        Dim nReg As Integer = CInt(cmdBusProCon.ExecuteScalar())
                        ' creo el registro del ticket
                        Dim sInstr As String = "INSERT INTO dbo.CabeceraProvision(codubi, tiptic, nrotic, fchasn, codcta, codanl, codaux, codtdc, nrodoc, fchemi, diaven, montca, nroo_c, tipmon, monmna, monmex, igvmna, igvmex, iscmna, iscmex, anomes, codasn, nroasn, nroano, ticrel, flgcam, flganu) VALUES (@codubi, @tiptic, @nrotic, @fchasn, @codcta, @codanl, @codaux, @codtdc, @nrodoc, @fchemi, @diaven, @montca, @nroo_c, @tipmon, @monmna, @monmex, @igvmna, @igvmex, @iscmna, @iscmex, @anomes, @codasn, @nroasn, @nroano, @ticrel, @flgcam, @flganu)"
                        If nReg > 0 Then
                            'Si existe el ticket, actualizo el registro del ticket
                            sInstr = "UPDATE dbo.CabeceraProvision SET fchasn=@fchasn, codcta=@codcta, codanl=@codanl, codaux=@codaux, codtdc=@codtdc, nrodoc=@nrodoc, fchemi=@fchemi, diaven=@diaven, montca=@montca, nroo_c=@nroo_c, tipmon=@tipmon, monmna=@monmna, monmex=@monmex, igvmna=@igvmna, igvmex=@igvmex, iscmna=@iscmna, iscmex=@iscmex, anomes=@anomes, codasn=@codasn, nroasn=@nroasn, nroano=@nroano, ticrel=@ticrel, flgcam=@flgcam, flganu=@flganu WHERE (codubi='L') AND (tiptic='P') AND (nrotic=@nrotic)"
                            lFlgNuevo = False
                        End If
                        Dim cmdActProCon As New SqlCommand(sInstr, cnctb)
                        Using cmdActProCon
                            cmdActProCon.Parameters.AddWithValue("@codubi", "L")
                            cmdActProCon.Parameters.AddWithValue("@tiptic", "P")
                            cmdActProCon.Parameters.AddWithValue("@nrotic", pProvision.NroTicket)
                            cmdActProCon.Parameters.AddWithValue("@fchasn", pProvision.FchIngreso)
                            cmdActProCon.Parameters.AddWithValue("@codcta", DBNull.Value)
                            cmdActProCon.Parameters.AddWithValue("@codanl", pProvision.CodAnalisis)
                            cmdActProCon.Parameters.AddWithValue("@codaux", "") ' origen+destino
                            cmdActProCon.Parameters.AddWithValue("@codtdc", pProvision.TipDocumento)
                            cmdActProCon.Parameters.AddWithValue("@nrodoc", pProvision.NroDocumento)
                            cmdActProCon.Parameters.AddWithValue("@fchemi", pProvision.FchEmision)
                            cmdActProCon.Parameters.AddWithValue("@diaven", pProvision.NroConPago)
                            cmdActProCon.Parameters.AddWithValue("@montca", pProvision.MonTipoCambio)
                            cmdActProCon.Parameters.AddWithValue("@nroo_c", Strings.Left(pProvision.FchVuelo.ToString, 10))
                            cmdActProCon.Parameters.AddWithValue("@tipmon", pProvision.CodMoneda)
                            cmdActProCon.Parameters.AddWithValue("@monmna", IIf(pProvision.CodMoneda = "S", pProvision.MonTotal, 0))
                            cmdActProCon.Parameters.AddWithValue("@monmex", IIf(pProvision.CodMoneda = "S", 0, pProvision.MonTotal))
                            cmdActProCon.Parameters.AddWithValue("@igvmna", 0)
                            cmdActProCon.Parameters.AddWithValue("@igvmex", 0)
                            cmdActProCon.Parameters.AddWithValue("@iscmna", 0)
                            cmdActProCon.Parameters.AddWithValue("@iscmex", 0)
                            cmdActProCon.Parameters.AddWithValue("@anomes", pProvision.NroPeriodo)
                            cmdActProCon.Parameters.AddWithValue("@codasn", pProvision.CodAsiento)
                            cmdActProCon.Parameters.AddWithValue("@nroasn", pProvision.NroAsiento)
                            cmdActProCon.Parameters.AddWithValue("@nroano", Strings.Left(pProvision.NroPeriodo, 4))
                            cmdActProCon.Parameters.AddWithValue("@ticrel", IIf(pProvision.FlgDetraccion = True, "D", DBNull.Value))
                            cmdActProCon.Parameters.AddWithValue("@flgcam", False)
                            cmdActProCon.Parameters.AddWithValue("@flganu", False)
                            cmdActProCon.Transaction = oTranCtb
                            cmdActProCon.ExecuteNonQuery()
                        End Using
                    End Using
                    ' Borro el detalle de la provision si ya existe
                    If lFlgNuevo = False Then
                        Dim cmdDetProv As New SqlCommand("DELETE FROM dbo.DetalleProvision WHERE (CodUbi='L') AND (tiptic='P') AND (nrotic=@NroTicket)", cnctb)
                        Using cmdDetProv
                            cmdDetProv.Parameters.AddWithValue("@NroTicket", pProvision.NroTicket)
                            cmdDetProv.Transaction = oTranCtb
                            cmdDetProv.ExecuteNonQuery()
                        End Using
                    End If
                    oTranCtb.Commit()
                    cnctb.Close()
                    Return True
                Catch myException As Exception
                    cn.Close()
                    cnctb.Close()
                    MsgBox("ConsolidadoADO (pr_ActualizarProvision) --> " & myException.Message, MsgBoxStyle.Critical)
                    Return False
                End Try
            End Using
        End Using
    End Function

    Public Function pr_ActualizarProvisionManifiestoDetalle(ByVal pDProvision As D_ManifiestoBE, ByVal sTicket As String) As Boolean
        Dim oTranCtb As SqlTransaction
        Using cnCtb As New SqlConnection(ConexionADO.GetCnxContabilidad)
            Dim oTran As SqlTransaction
            Using cn As New SqlConnection(MiConexion.GetCnx)
                Try
                    ' Actualizo el detalle del manifiesto
                    cn.Open()
                    oTran = cn.BeginTransaction(IsolationLevel.Serializable)
                    Dim cmdDetManifiesto As New SqlCommand("Update dbo.D_Manifiesto Set NroItem=@Item,CodComunidad=@Comunidad,MonPasaje=@Pasaje,FlgNoEditar=@NoEditar WHERE (CodViaje=@CodVuelo) AND (CodReserva=@Reserva)", cn)
                    Using cmdDetManifiesto
                        cmdDetManifiesto.Parameters.AddWithValue("@CodVuelo", pDProvision.CodViaje.Trim)
                        cmdDetManifiesto.Parameters.AddWithValue("@Reserva", pDProvision.CodReserva)
                        cmdDetManifiesto.Parameters.AddWithValue("@Item", pDProvision.NroItem)
                        cmdDetManifiesto.Parameters.AddWithValue("@Comunidad", IIf(pDProvision.CodComunidad.Trim Is String.Empty, "0000000", pDProvision.CodComunidad.Trim))
                        cmdDetManifiesto.Parameters.AddWithValue("@Pasaje", pDProvision.MonPasaje)
                        cmdDetManifiesto.Parameters.AddWithValue("@NoEditar", pDProvision.FlgNoEditar)
                        cmdDetManifiesto.Transaction = oTran
                        cmdDetManifiesto.ExecuteNonQuery()
                    End Using
                    oTran.Commit()
                    cn.Close()
                    ' Actualizo el detalle de la provision en la contabilidad
                    cnCtb.Open()
                    oTranCtb = cnCtb.BeginTransaction(IsolationLevel.Serializable)
                    Dim cmdActProCon As New SqlCommand("INSERT INTO dbo.DetalleProvision(codubi, tiptic, nrotic, numitm, codmat, desmat, undcmp, codprv, codtdc, nrodoc, fchemi, canmat, nacmat, usamat, porimp, pordes, porsel, codact, flgcam) VALUES (@codubi, @tiptic, @nrotic, @numitm, @codmat, @desmat, @undcmp, @codprv, @codtdc, @nrodoc, @fchemi, @canmat, @nacmat, @usamat, @porimp, @pordes, @porsel, @codact, @flgcam)", cnCtb)
                    Using cmdActProCon
                        Dim sNomApe As String = String.Format("{0} {1} {2}", pDProvision.NomPasajero.Trim, pDProvision.AppPasajero.Trim, pDProvision.ApmPasajero.Trim)
                        cmdActProCon.Parameters.AddWithValue("@codubi", "L")
                        cmdActProCon.Parameters.AddWithValue("@tiptic", "P")
                        cmdActProCon.Parameters.AddWithValue("@nrotic", sTicket)
                        cmdActProCon.Parameters.AddWithValue("@numitm", pDProvision.NroItem)
                        cmdActProCon.Parameters.AddWithValue("@codmat", pDProvision.NroDocumentoId)
                        cmdActProCon.Parameters.AddWithValue("@desmat", sNomApe)
                        cmdActProCon.Parameters.AddWithValue("@undcmp", pDProvision.CodDestino)
                        cmdActProCon.Parameters.AddWithValue("@codprv", pDProvision.CodAnalisis)
                        cmdActProCon.Parameters.AddWithValue("@codtdc", pDProvision.TipPasajero)
                        cmdActProCon.Parameters.AddWithValue("@nrodoc", pDProvision.CodViaje)
                        cmdActProCon.Parameters.AddWithValue("@fchemi", DBNull.Value)
                        cmdActProCon.Parameters.AddWithValue("@canmat", pDProvision.CodCentroCosto2)
                        cmdActProCon.Parameters.AddWithValue("@nacmat", pDProvision.MonPasaje)
                        cmdActProCon.Parameters.AddWithValue("@usamat", IIf(pDProvision.FlgChecking = False, 1, DBNull.Value))
                        cmdActProCon.Parameters.AddWithValue("@porimp", pDProvision.PctCentroCosto)
                        cmdActProCon.Parameters.AddWithValue("@pordes", pDProvision.CodOrigen)
                        cmdActProCon.Parameters.AddWithValue("@porsel", pDProvision.CodComunidad)
                        cmdActProCon.Parameters.AddWithValue("@codact", pDProvision.CodCentroCosto)
                        cmdActProCon.Parameters.AddWithValue("@flgcam", True)
                        cmdActProCon.Transaction = oTranCtb
                        cmdActProCon.ExecuteNonQuery()
                    End Using
                    oTranCtb.Commit()
                    cnCtb.Close()
                    Return True
                Catch myException As Exception
                    cn.Close()
                    cnCtb.Close()
                    MsgBox("ConsolidadoADO (pr_ActualizarProvisionManifiestoDetalle) --> " & myException.Message, MsgBoxStyle.Critical)
                    Return False
                End Try
            End Using
        End Using
    End Function

    Public Function pr_ActualizarProvisionManifiestoProducto(ByVal sVuelo As String, ByVal lFlag As Boolean) As Boolean
        Using cn As New SqlConnection(MiConexion.GetCnx)
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Try
                Dim mySqlCommand As SqlCommand = New SqlCommand("Update dbo.C_Manifiesto Set FlgProducto=@lProducto WHERE (CodViaje=@CodVuelo)", cn) With {.Transaction = oTran}
                mySqlCommand.Parameters.AddWithValue("@CodVuelo", sVuelo.Trim)
                mySqlCommand.Parameters.AddWithValue("@lProducto", lFlag)
                mySqlCommand.ExecuteNonQuery()
                oTran.Commit()
                cn.Close()
                Return True
            Catch myException As Exception
                oTran.Rollback()
                cn.Close()
                MsgBox("ConsolidadoADO (pr_ActualizarProvisionManifiestoProducto) --> " & myException.Message, MsgBoxStyle.Critical)
                Return False
            End Try
        End Using
    End Function

    Public Function pr_AsignaTicketManifiestoCabecera(ByVal sVuelo As String, ByVal sTicket As String) As Boolean
        Dim oTran As SqlTransaction
        Using cn As New SqlConnection(MiConexion.GetCnx)
            Try
                cn.Open()
                oTran = cn.BeginTransaction(IsolationLevel.Serializable)
                Dim mySqlCommand As SqlCommand = New SqlCommand("UPDATE dbo.C_Manifiesto SET NroTicket=@NroTicket,EstadoManifiesto=@Estado WHERE (CodViaje=@CodVuelo)", cn) With {.Transaction = oTran}
                mySqlCommand.Parameters.AddWithValue("@CodVuelo", sVuelo.Trim)
                mySqlCommand.Parameters.AddWithValue("@NroTicket", IIf(sTicket.Trim Is String.Empty, DBNull.Value, sTicket.Trim))
                mySqlCommand.Parameters.AddWithValue("@Estado", IIf(sTicket.Trim Is String.Empty, "2", "3"))
                mySqlCommand.ExecuteNonQuery()
                oTran.Commit()
                cn.Close()
                Return True
            Catch myException As Exception
                cn.Close()
                MsgBox("ConsolidadoADO (pr_ActualizarProvisionManifiestoCabecera) --> " & myException.Message, MsgBoxStyle.Critical)
                Return False
            End Try
        End Using
    End Function

    Public Function pr_BuscarCorrelativo(ByVal sAnoMes As String, sCodAsn As String) As String
        Dim snroasn As String = "0001"
        Dim sql As String = String.Format("select anomes,codasn,nroasn+1 as nroasn from dbo.correlativo where anomes='{0}' AND codasn='{1}'", sAnoMes, sCodAsn)
        Using cn As New SqlConnection(ConexionADO.GetCnxContabilidad)
            Try
                cn.Open()
                Using cmd As New SqlCommand(sql, cn)
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Using dt As DataTable = New DataTable("tasn")
                        da.Fill(dt)
                        Dim drCurrent As DataRow
                        For Each drCurrent In dt.Rows
                            snroasn = drCurrent("nroasn")
                            snroasn = Strings.Right("000" & snroasn.Trim, 4)
                        Next
                    End Using
                End Using
                cn.Close()
                Return snroasn
            Catch ex As Exception
                cn.Close()
                MsgBox("ConsolidadoADO (pr_BuscarCorrelativo) --> " & ex.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function

    Public Function pr_CargarCabeceraManifiesto(ByVal sFchVuelo As String) As DataTable
        Dim sdia As String = Strings.Mid(sFchVuelo, 1, 2)
        Dim smes As String = Strings.Mid(sFchVuelo, 4, 2)
        Dim sanio As String = Strings.Mid(sFchVuelo, 7, 4)
        Dim sql As String = "SELECT m.CodViaje AS CodVuelo,DesTramo=lo.NomLugar+'-'+ld.NomLugar,m.FchViaje,m.EstadoManifiesto,m.FlgValija,ISNULL(m.FlgProducto,0) AS FlgProducto,CAST(CASE m.EstadoManifiesto WHEN '3' THEN 1 ELSE 0 END AS BIT) AS FlgSeleccion FROM dbo.c_manifiesto m " &
                            "INNER JOIN dbo.m_lugar lo ON 'L'+m.CodlugarOrigen=lo.TipLugar+lo.CodLugar INNER JOIN dbo.m_lugar ld on 'L'+m.CodlugarDestino=lo.TipLugar+ld.CodLugar " &
                            "WHERE (year(m.fchViaje)= " & sanio & " And Month(m.fchViaje) = " & smes & " And Day(m.fchViaje) = " & sdia & ") " & "AND m.EstadoManifiesto in ('2','3')"
        Using cn As New SqlConnection(MiConexion.GetCnx)
            Try
                Dim dt As New DataTable
                cn.Open()
                Using da As New SqlDataAdapter(sql, cn)
                    da.Fill(dt)
                End Using
                cn.Close()
                Return dt
            Catch myException As Exception
                cn.Close()
                MsgBox("ConsolidadoADO (pr_CargarCabeceraManifiesto) --> " & myException.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function

    Public Function pr_CargaCorrelativoTicket() As String
        Dim rowsupdate As Integer
        Dim intkey As Integer
        Dim stick As String
        Using cn As New SqlConnection(MiConexion.GetCnx)
            Try
                cn.Open()
                Using xcmd As New SqlCommand("pr_nroticket", cn)
                    With xcmd
                        .CommandType = CommandType.StoredProcedure
                        .Parameters.Add("@onrotic", SqlDbType.Int)
                        .Parameters(0).Direction = ParameterDirection.Output
                        rowsupdate = .ExecuteNonQuery
                        If IsDBNull(.Parameters(0).Value) Then
                            intkey = 1
                        Else
                            intkey = .Parameters(0).Value
                        End If
                        stick = "00000000" & CType(intkey, String)
                    End With
                End Using
                cn.Close()
                Return Strings.Right(stick, 8)
            Catch ex As Exception
                cn.Close()
                MsgBox("ConsolidadoADO (pr_CargaCorrelativoTicket) --> " & ex.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function

    Public Function pr_CargarDetalleManifiesto(ByVal sTicket As String) As DataTable
        Dim dt As New DataTable
        Dim sql As String = "select m.CodViaje as CodVuelo,DesTramo=lo.NomLugar+'-'+ld.NomLugar,m.CodReserva,m.CodAnalisis,ISNULL(CASE m.CodComunidad WHEN '0000000' THEN '       ' END, '       ') AS CodComunidad,m.NroDocumentoId," &
                            "c.CodLugarOrigen as CodOrigen,c.CodLugarDestino as CodDestino,m.AppPasajero,m.ApmPasajero,m.NomPasajero,ApeNomPasajero=m.AppPasajero+' '+m.ApmPasajero+', '+m.NomPasajero," &
                            "r.TipPasajero,m.NacionalidadPasajero,m.TipPorExcepcion,m.ObsManifiesto,m.CodCentroCosto,m.PctCentroCosto,m.CodCentroCosto2,tp.NomTipoPasajero,isnull(m.MonPasaje,0) as MonPasaje," &
                            "ru.PrePasaje,FlgNoEditar = CAST(isnull(m.FlgNoEditar, 0) AS BIT),m.FlgChecking,m.FlgLock,m.FlgResponsable,m.FlgChecking from d_manifiesto m inner join dbo.V_Reserva r on m.CodReserva=r.CodReserva " &
                            "inner join dbo.M_TipoPasajero tp on r.TipPasajero=tp.CodTipoPasajero inner join dbo.C_Manifiesto c on m.CodViaje=c.CodViaje " &
                            "inner join dbo.m_Rutas ru on c.CodLugarOrigen=ru.CodOrigen and c.CodLugarDestino=ru.CodDestino inner join dbo.m_lugar lo on 'L'+c.CodlugarOrigen=lo.TipLugar+lo.CodLugar " &
                            "inner join dbo.m_lugar ld on 'L'+c.CodlugarDestino=lo.TipLugar+ld.CodLugar where (c.NroTicket='" & sTicket & "') order by m.codViaje,m.NroItem"
        Using cn As New SqlConnection(MiConexion.GetCnx)
            Try
                cn.Open()
                Using da As New SqlDataAdapter(sql, cn)
                    da.Fill(dt)
                End Using
                cn.Close()
                Return dt
            Catch myException As Exception
                cn.Close()
                MsgBox("ConsolidadoADO (pr_CargarDetalleManifiesto) --> " & myException.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function

    Public Function pr_GenerarAsiento(ByVal sTicket As String, sAnoMes As String, sCodAsn As String, sNroAsn As String, ByVal scodusr As String) As String
        Try
            ' si es nuevo se busca el correlativo
            If sNroAsn.Trim = "" Then
                sNroAsn = pr_BuscarCorrelativo(sAnoMes, sCodAsn)
            End If
            ' si se graba bien el correlativo y provision
            If pr_GrabarCorrelativo(sTicket, sAnoMes, sCodAsn, sNroAsn) Then
                ' traemos la provision, cabecera manifiesto y detalle manifiesto
                Dim sql As String = "SELECT vp.nroTicket,vp.nroPeriodo,vp.codAsiento,vp.nroAsiento,vp.tipDocumento,vp.nroDocumento,vp.fchIngreso,vp.fchEmision,vp.fchvuelo,vp.monTipoCambio,vp.codMoneda,vp.monTotal,vp.nroConPago," &
                                    "vp.flgDetraccion,et.codAnalisis as codEmpresa,et.nomEmpresa,cm.codviaje,left(lo.nomlugar,3) as nomlugarorigen,left(ld.nomlugar,3) as nomlugardestino,cm.flgproducto,dm.codCentroCosto,dm.pctCentroCosto," &
                                    "tp.codgasto,tp.coduga,dm.codCentroCosto2,dm.codAnalisis,dm.codComunidad,dm.appPasajero+' '+dm.apmPasajero+', '+dm.nomPasajero as nomPasajero,dm.monPasaje FROM dbo.V_Provision vp " &
                                    "INNER JOIN dbo.m_empresatransporte et ON vp.codempresa=et.codempresa INNER JOIN dbo.c_manifiesto cm ON vp.nroticket=cm.nroticket " &
                                    "INNER JOIN dbo.d_manifiesto dm ON cm.codviaje=dm.codviaje INNER JOIN dbo.v_reserva re ON dm.codreserva=re.codreserva " &
                                    "INNER JOIN m_tipopasajero tp ON re.tipPasajero=tp.codTipoPasajero INNER JOIN dbo.m_lugar lo ON cm.codlugarorigen=lo.codlugar " &
                                    "INNER JOIN dbo.m_lugar ld ON cm.codlugardestino=ld.codlugar WHERE (vp.nroticket='" & sTicket & "')"
                Using dt As New DataTable()
                    Using cn As New SqlConnection(MiConexion.GetCnx)
                        cn.Open()
                        Using da As New SqlDataAdapter(sql, cn)
                            da.Fill(dt)
                        End Using
                        cn.Close()
                        Dim lflgOro As Boolean = False
                        Dim lflgDetraccion As Boolean = False
                        Dim scodmoneda As String = ""
                        Dim scodempresa As String = ""
                        Dim snomempresa As String = ""
                        Dim sruta As String = ""
                        Dim ndiaven As Integer
                        Dim nregtot As Integer = 2
                        Dim nregistro As Integer = 0
                        Dim nigvmna As Decimal = 0
                        Dim nigvmex As Decimal = 0
                        Dim ndetmna As Decimal = 0
                        Dim ndetmex As Decimal = 0
                        Dim nmontca As Decimal = 0
                        Dim ntotmna As Decimal = 0
                        Dim ntotmex As Decimal = 0
                        Dim nacumna As Decimal = 0
                        Dim nacumex As Decimal = 0
                        Dim ndebmna As Decimal = 0
                        Dim ndebmex As Decimal = 0
                        Dim AsntBE As New V_AsientoBE()
                        'recorro el dt para cargar todos los pasajeros
                        For Each fila As DataRow In dt.Rows
                            nregistro = nregistro + 1
                            If fila("codMoneda") = "D" Then
                                ndebmna = Math.Round(fila("monPasaje") * fila("monTipoCambio") * (fila("pctCentroCosto") / 100), 2)
                                ndebmex = Math.Round(fila("monPasaje") * (fila("pctCentroCosto") / 100), 2)
                            Else
                                ndebmna = Math.Round(fila("monPasaje") * (fila("pctCentroCosto") / 100), 2)
                            End If
                            nacumna = nacumna + ndebmna
                            nacumex = nacumex + ndebmex
                            Dim sfchvuelo As String = fila("fchvuelo").ToString
                            AsntBE.anomes = fila("nroPeriodo")
                            AsntBE.codasn = fila("codAsiento")
                            AsntBE.nroasn = fila("nroAsiento")
                            AsntBE.registro = Strings.Right("    " & nregistro.ToString, 4)
                            AsntBE.codcta = fila("codCentroCosto")
                            AsntBE.codgas = fila("codgasto")
                            AsntBE.coduga = fila("coduga")
                            AsntBE.codanl = fila("codAnalisis")
                            AsntBE.codaux = fila("codComunidad")
                            AsntBE.fchemi = fila("fchEmision")
                            AsntBE.codtdc = fila("tipDocumento")
                            AsntBE.nrodoc = fila("nroDocumento")
                            AsntBE.reflin = String.Format("{0}-{1} {2} {3}", fila("nomlugarorigen"), fila("nomlugardestino"), Strings.Left(sfchvuelo, 5), fila("nomPasajero"))
                            AsntBE.diaven = 0
                            AsntBE.debmna = ndebmna
                            AsntBE.habmna = 0
                            AsntBE.debmex = ndebmex
                            AsntBE.habmex = 0
                            AsntBE.montca = fila("monTipoCambio")
                            AsntBE.fchasn = fila("fchIngreso")
                            AsntBE.creusr = scodusr
                            AsntBE.crefec = Date.Today
                            AsntBE.registros = Strings.Right("    " & dt.Rows.Count.ToString, 4)
                            AsntBE.montotal = fila("monTotal")
                            AsntBE.desglo = String.Format("PROVISIÓN POR TRASLADO AÉREO DEL VUELO {0} TICKET No {1}", fila("codviaje"), sTicket)
                            If nregistro = 1 Then
                                sruta = String.Format("{0}-{1}", fila("nomlugarorigen"), fila("nomlugardestino"))
                                lflgOro = fila("flgproducto")          ' indicamos si hay oro
                                lflgDetraccion = fila("flgDetraccion") ' verificamos si hay detraccion 
                                scodempresa = fila("codEmpresa")
                                snomempresa = fila("nomEmpresa")
                                scodmoneda = fila("codMoneda")
                                ndiaven = fila("nroConPago")
                                nmontca = fila("monTipoCambio")
                                nregtot = nregtot + IIf(lflgOro = True, 1, 0)
                                nregtot = nregtot + IIf(lflgDetraccion = True, 2, 0)
                                If fila("codMoneda") = "D" Then
                                    ntotmna = fila("monTotal") * fila("monTipoCambio")
                                    ntotmex = fila("monTotal")
                                Else
                                    ntotmna = fila("monTotal")
                                End If
                                nigvmna = Math.Round(ntotmna * 0.18, 2)
                                nigvmex = Math.Round(ntotmex * 0.18, 2)
                                ndetmna = Math.Round((ntotmna + nigvmna) * 0.09, 2)
                                ndetmex = Math.Round((ntotmex + nigvmex) * 0.09, 2)
                                nregtot = nregtot + dt.Rows.Count
                                AsntBE.registros = Strings.Right("    " & nregtot.ToString, 4)
                                AsntBE.montotal = ntotmna + nigvmna + ndetmna
                                ' Grabo la cabecera
                                pr_GrabarAsientoCabecera(AsntBE)
                            End If
                            ' Grabo el detalle
                            pr_GrabarAsientoDetalle(AsntBE)
                            If fila("pctCentroCosto") < 100 Then
                                nregistro = nregistro + 1
                                If fila("codMoneda") = "D" Then
                                    ndebmna = Math.Round((fila("monPasaje") * fila("monTipoCambio")) - ndebmna, 2)
                                    ndebmex = Math.Round(fila("monPasaje") - ndebmex, 2)
                                Else
                                    ndebmna = Math.Round(fila("monPasaje") - ndebmna, 2)
                                End If
                                nacumna = nacumna + ndebmna
                                nacumex = nacumex + ndebmex
                                AsntBE.registro = Strings.Right("    " & nregistro.ToString, 4)
                                AsntBE.codcta = fila("codCentroCosto2")
                                AsntBE.debmna = ndebmna
                                AsntBE.debmex = ndebmex
                                pr_GrabarAsientoDetalle(AsntBE)
                            End If
                        Next
                        ' si se ha transportado oro, cargo el resto de la factura a este concepto
                        If lflgOro = True Then
                            nregistro = nregistro + 1
                            AsntBE.registro = Strings.Right("    " & nregistro.ToString, 4)
                            AsntBE.codanl = "385485"
                            AsntBE.codcta = "9360101"
                            AsntBE.codgas = "6311102"
                            AsntBE.codaux = ""
                            AsntBE.reflin = "TRANSPORTE MINERAL DE PRECIPITADO"
                            AsntBE.debmna = ntotmna - nacumna
                            AsntBE.debmex = ntotmex - nacumex
                            pr_GrabarAsientoDetalle(AsntBE)
                        End If
                        ' cargo el igv de la factura
                        nregistro = nregistro + 1
                        AsntBE.registro = Strings.Right("    " & nregistro.ToString, 4)
                        AsntBE.codanl = ""
                        AsntBE.codcta = "4011103"
                        AsntBE.codgas = ""
                        AsntBE.codaux = ""
                        AsntBE.reflin = snomempresa
                        AsntBE.debmna = nigvmna
                        AsntBE.debmex = nigvmex
                        pr_GrabarAsientoDetalle(AsntBE)
                        ' abono el total de la factura
                        nregistro = nregistro + 1
                        AsntBE.registro = Strings.Right("    " & nregistro.ToString, 4)
                        AsntBE.codanl = scodempresa
                        AsntBE.codcta = "4212101"
                        AsntBE.codgas = ""
                        AsntBE.codaux = ""
                        AsntBE.reflin = String.Format("PSJE.{0} DEL {1}", sruta, Strings.Left(AsntBE.fchemi.ToString, 5))
                        AsntBE.diaven = ndiaven
                        AsntBE.debmna = 0
                        AsntBE.debmex = 0
                        AsntBE.habmna = ntotmna + nigvmna
                        AsntBE.habmex = ntotmex + nigvmex
                        pr_GrabarAsientoDetalle(AsntBE)
                        ' si se realiza detraccion
                        If lflgDetraccion = True Then
                            nregistro = nregistro + 1
                            AsntBE.registro = Strings.Right("    " & nregistro.ToString, 4)
                            AsntBE.codanl = scodempresa
                            AsntBE.codcta = "4212101"
                            AsntBE.codgas = ""
                            AsntBE.codaux = ""
                            AsntBE.reflin = "6-DETRACCION"
                            AsntBE.diaven = ndiaven
                            AsntBE.debmna = ndetmna
                            AsntBE.debmex = ndetmex
                            AsntBE.habmna = 0
                            AsntBE.habmex = 0
                            pr_GrabarAsientoDetalle(AsntBE)
                            nregistro = nregistro + 1
                            AsntBE.registro = Strings.Right("    " & nregistro.ToString, 4)
                            AsntBE.codanl = scodempresa
                            AsntBE.codcta = "4212109"
                            AsntBE.codgas = ""
                            AsntBE.codaux = "X00041"
                            AsntBE.reflin = "6-DETRACCION"
                            AsntBE.diaven = ndiaven
                            AsntBE.debmna = 0
                            AsntBE.debmex = 0
                            AsntBE.habmna = ndetmna
                            AsntBE.habmex = ndetmex
                            pr_GrabarAsientoDetalle(AsntBE)
                        End If
                    End Using
                End Using
            End If
            Return String.Format("{0}-{1}", sCodAsn, sNroAsn)
        Catch myException As Exception
            MsgBox("ConsolidadoADO (pr_GenerarAsiento) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_GrabarAsientoCabecera(ByVal vAsn As V_AsientoBE) As Boolean
        Dim lFlgNuevo As Boolean = True
        Dim oTranCtb As SqlTransaction
        Using cnctb As New SqlConnection(ConexionADO.GetCnxContabilidad)
            Try
                cnctb.Open()
                oTranCtb = cnctb.BeginTransaction(IsolationLevel.Serializable)
                Dim cmdBusCab As New SqlCommand("SELECT count(*) FROM dbo.Cabecera WHERE (anomes=@anomes) AND (codasn=@codasn) AND (nroasn=@nroasn)", cnctb)
                Using cmdBusCab
                    cmdBusCab.CommandType = CommandType.Text
                    cmdBusCab.Parameters.Add("@anomes", SqlDbType.Char, 6)
                    cmdBusCab.Parameters("@anomes").Value = vAsn.anomes
                    cmdBusCab.Parameters.Add("@codasn", SqlDbType.Char, 3)
                    cmdBusCab.Parameters("@codasn").Value = vAsn.codasn
                    cmdBusCab.Parameters.Add("@nroasn", SqlDbType.Char, 4)
                    cmdBusCab.Parameters("@nroasn").Value = vAsn.nroasn
                    cmdBusCab.Transaction = oTranCtb
                    Dim nReg As Integer = CInt(cmdBusCab.ExecuteScalar())
                    Dim sInstr As String = "INSERT INTO dbo.cabecera (anomes,codasn,nroasn,registro,fchasn,desglo,impdeb,imphab,fchmov,montca,creusr,crefec) VALUES (@anomes,@codasn,@nroasn,@registro,@fchasn,@desglo,@impdeb,@imphab,@fchmov,@montca,@creusr,@crefec)"
                    If nReg > 0 Then
                        sInstr = "UPDATE dbo.cabecera SET registro=@registro,fchasn=@fchasn,desglo=@desglo,impdeb=@impdeb,imphab=@imphab,fchmov=@fchmov,montca=@montca,creusr=@creusr,crefec=@crefec WHERE (anomes=@anomes) AND (codasn=@codasn) AND (nroasn=@nroasn)"
                        lFlgNuevo = False
                    End If
                    Dim sfchmov As String = vAsn.fchasn.ToString
                    Dim cmdActCab As New SqlCommand(sInstr, cnctb)
                    Using cmdActCab
                        cmdActCab.Parameters.AddWithValue("@anomes", vAsn.anomes)
                        cmdActCab.Parameters.AddWithValue("@codasn", vAsn.codasn)
                        cmdActCab.Parameters.AddWithValue("@nroasn", vAsn.nroasn)
                        cmdActCab.Parameters.AddWithValue("@registro", vAsn.registros)
                        cmdActCab.Parameters.AddWithValue("@fchasn", vAsn.fchasn)
                        cmdActCab.Parameters.AddWithValue("@desglo", vAsn.desglo)
                        cmdActCab.Parameters.AddWithValue("@impdeb", vAsn.montotal)
                        cmdActCab.Parameters.AddWithValue("@imphab", vAsn.montotal)
                        cmdActCab.Parameters.AddWithValue("@fchmov", Strings.Mid(sfchmov, 7, 4) & Strings.Mid(sfchmov, 4, 2) & Strings.Mid(sfchmov, 1, 2))
                        cmdActCab.Parameters.AddWithValue("@montca", vAsn.montca)
                        cmdActCab.Parameters.AddWithValue("@creusr", vAsn.creusr)
                        cmdActCab.Parameters.AddWithValue("@crefec", vAsn.crefec)
                        cmdActCab.Transaction = oTranCtb
                        cmdActCab.ExecuteNonQuery()
                    End Using
                End Using
                ' Borro el detalle del asiento si ya existe
                If lFlgNuevo = False Then
                    Dim cmdActDet As New SqlCommand("DELETE FROM dbo.Detalle WHERE (anomes=@anomes) AND (codasn=@codasn) AND (nroasn=@nroasn)", cnctb)
                    Using cmdActDet
                        cmdActDet.Parameters.AddWithValue("@anomes", vAsn.anomes)
                        cmdActDet.Parameters.AddWithValue("@codasn", vAsn.codasn)
                        cmdActDet.Parameters.AddWithValue("@nroasn", vAsn.nroasn)
                        cmdActDet.Transaction = oTranCtb
                        cmdActDet.ExecuteNonQuery()
                    End Using
                End If
                oTranCtb.Commit()
                cnctb.Close()
                Return True
            Catch ex As Exception
                cnctb.Close()
                MsgBox("ConsolidadoADO (pr_GrabarAsientoCabecera) --> " & ex.Message, MsgBoxStyle.Critical)
                Return False
            End Try
        End Using
    End Function

    Public Function pr_GrabarAsientoDetalle(ByVal vAsn As V_AsientoBE) As Boolean
        ' grabo la linea del asiento
        Using cn As New SqlConnection(ConexionADO.GetCnxContabilidad)
            Try
                cn.Open()
                Dim oTran As SqlTransaction = cn.BeginTransaction
                Const sInstruccion As String = "INSERT INTO dbo.detalle (anomes,codasn,nroasn,registro,idregistro,codcta,codgas,coduga,codanl,codaux,fchemi,codtdc,nrodoc,reflin,diaven,debmna,habmna,debmex,habmex,montca,fchasn,creusr,crefec) VALUES (@anomes,@codasn,@nroasn,@registro,@idregistro,@codcta,@codgas,@coduga,@codanl,@codaux,@fchemi,@codtdc,@nrodoc,@reflin,@diaven,@debmna,@habmna,@debmex,@habmex,@montca,@fchasn,@creusr,@crefec)"
                Dim mySqlCommand As SqlCommand = New SqlCommand(sInstruccion, cn) With {.Transaction = oTran}
                mySqlCommand.Parameters.AddWithValue("@anomes", vAsn.anomes)
                mySqlCommand.Parameters.AddWithValue("@codasn", vAsn.codasn)
                mySqlCommand.Parameters.AddWithValue("@nroasn", vAsn.nroasn)
                mySqlCommand.Parameters.AddWithValue("@registro", vAsn.registro)
                mySqlCommand.Parameters.AddWithValue("@idregistro", " ")
                mySqlCommand.Parameters.AddWithValue("@codcta", vAsn.codcta)
                mySqlCommand.Parameters.AddWithValue("@codgas", IIf(vAsn.codgas.Trim = "", "0000000", vAsn.codgas))
                mySqlCommand.Parameters.AddWithValue("@coduga", IIf(vAsn.coduga.Trim = "", "00", vAsn.coduga))
                mySqlCommand.Parameters.AddWithValue("@codanl", IIf(vAsn.codanl.Trim = "", "0000000", vAsn.codanl))
                mySqlCommand.Parameters.AddWithValue("@codaux", IIf(vAsn.codaux.Trim = "", "0000000", vAsn.codaux))
                mySqlCommand.Parameters.AddWithValue("@fchemi", vAsn.fchemi)
                mySqlCommand.Parameters.AddWithValue("@codtdc", vAsn.codtdc)
                mySqlCommand.Parameters.AddWithValue("@nrodoc", vAsn.nrodoc)
                mySqlCommand.Parameters.AddWithValue("@reflin", Strings.Left(vAsn.reflin, 50))
                mySqlCommand.Parameters.AddWithValue("@diaven", vAsn.diaven)
                mySqlCommand.Parameters.AddWithValue("@debmna", vAsn.Debmna)
                mySqlCommand.Parameters.AddWithValue("@habmna", vAsn.Habmna)
                mySqlCommand.Parameters.AddWithValue("@debmex", vAsn.Debmex)
                mySqlCommand.Parameters.AddWithValue("@habmex", vAsn.Habmex)
                mySqlCommand.Parameters.AddWithValue("@montca", vAsn.Montca)
                mySqlCommand.Parameters.AddWithValue("@fchasn", vAsn.fchasn)
                mySqlCommand.Parameters.AddWithValue("@creusr", vAsn.Creusr)
                mySqlCommand.Parameters.AddWithValue("@crefec", vAsn.Crefec)
                mySqlCommand.ExecuteNonQuery()
                oTran.Commit()
                cn.Close()
                Return True
            Catch ex As Exception
                cn.Close()
                MsgBox("ConsolidadoADO (pr_GrabarAsientoDetalle) --> " & ex.Message, MsgBoxStyle.Critical)
                Return False
            End Try
        End Using
    End Function

    Public Function pr_GrabarCorrelativo(ByVal sTicket As String, sAnomes As String, sCodasn As String, sNroAsn As String) As Boolean
        Dim nnroasn As Integer = sNroAsn
        Dim oTran As SqlTransaction
        Using cn As New SqlConnection(MiConexion.GetCnx)
            Dim oTranCtb As SqlTransaction
            Using cnctb As New SqlConnection(ConexionADO.GetCnxContabilidad)
                Try
                    cnctb.Open()
                    oTranCtb = cnctb.BeginTransaction(IsolationLevel.Serializable)
                    ' grabo el correlativo en la contabilidad
                    Dim cmdBusCor As New SqlCommand("SELECT COUNT(*) FROM dbo.correlativo WHERE (anomes=@anomes) AND (codasn=@codasn)", cnctb)
                    Using cmdBusCor
                        cmdBusCor.CommandType = CommandType.Text
                        cmdBusCor.Parameters.Add("@anomes", SqlDbType.Char, 6)
                        cmdBusCor.Parameters("@anomes").Value = sAnomes
                        cmdBusCor.Parameters.Add("@codasn", SqlDbType.Char, 3)
                        cmdBusCor.Parameters("@codasn").Value = sCodasn
                        cmdBusCor.Transaction = oTranCtb
                        Dim nReg As Integer = CInt(cmdBusCor.ExecuteScalar())

                        ' creo el registro del ticket
                        Dim sInstr As String = "Insert Into dbo.correlativo (anomes, codasn, nroasn) Values (@anomes, @codasn, @nroasn)"
                        If nReg > 0 Then
                            'Si existe el asiento, actualizo el registro del correlativo
                            sInstr = "Update dbo.correlativo Set nroasn=@nroasn WHERE anomes=@anomes AND codasn=@codasn AND nroasn<@nroasn"
                        End If
                        Dim cmdActCor As New SqlCommand(sInstr, cnctb)
                        Using cmdActCor
                            cmdActCor.Parameters.AddWithValue("@anomes", sAnomes)
                            cmdActCor.Parameters.AddWithValue("@codasn", sCodasn)
                            cmdActCor.Parameters.AddWithValue("@nroasn", nnroasn)
                            cmdActCor.Transaction = oTranCtb
                            cmdActCor.ExecuteNonQuery()
                        End Using
                    End Using
                    ' grabo el correativo en la provision del ticket
                    Dim cmdActCab As New SqlCommand("Update dbo.cabeceraprovision Set nroasn=@nroAsiento WHERE (codubi='L') AND (tiptic='P') AND (nrotic=@nTicket)", cnctb)
                    Using cmdActCab
                        cmdActCab.Parameters.AddWithValue("@nroAsiento", sNroAsn)
                        cmdActCab.Parameters.AddWithValue("@nTicket", sTicket)
                        cmdActCab.Transaction = oTranCtb
                        cmdActCab.ExecuteNonQuery()
                    End Using
                    oTranCtb.Commit()
                    cnctb.Close()
                    ' grabo el correlativo en la provision de pasajeros
                    cn.Open()
                    oTran = cn.BeginTransaction(IsolationLevel.Serializable)
                    Dim cmdActPro As New SqlCommand("Update dbo.V_Provision Set NroAsiento=@nroAsiento WHERE (NroTicket=@nTicket)", cn)
                    Using cmdActPro
                        cmdActPro.Parameters.AddWithValue("@nroAsiento", sNroAsn)
                        cmdActPro.Parameters.AddWithValue("@nTicket", sTicket)
                        cmdActPro.Transaction = oTran
                        cmdActPro.ExecuteNonQuery()
                    End Using
                    oTran.Commit()
                    cn.Close()
                    Return True
                Catch myException As Exception
                    cn.Close()
                    cnctb.Close()
                    MsgBox("ConsolidadoADO (pr_GrabarCorrelativo) --> " & myException.Message, MsgBoxStyle.Critical)
                    Return False
                End Try
            End Using
        End Using
    End Function

    Public Function pr_ListarConsolidado(ByVal sFiltro As String) As DataTable
        Dim sql As String = "SELECT p.NroPeriodo, p.CodAsiento+p.NroAsiento as Asiento, p.NroTicket, p.TipDocumento, p.NroDocumento, p.FchIngreso, p.FchEmision, p.FchVuelo," &
                            "p.NroConPago, p.MonTipoCambio, p.CodMoneda, m.NomMoneda, p.MonTotal, p.CodEmpresa, e.CodAnalisis, e.NomEmpresa as DesAnalisis FROM dbo.V_Provision p " &
                            "INNER JOIN M_EmpresaTransporte e on p.codEmpresa=e.CodEmpresa INNER JOIN M_Moneda m ON p.CodMoneda=m.CodMoneda"
        Dim scriterio As String = IIf(sFiltro.Trim() = "", "", " Where " & Trim(sFiltro))
        Dim dt As New DataTable
        Using cn As New SqlConnection(MiConexion.GetCnx)
            Try
                cn.Open()
                Using da As New SqlDataAdapter(sql & scriterio, cn)
                    da.Fill(dt)
                End Using
                cn.Close()
                Return dt
            Catch myException As Exception
                cn.Close()
                MsgBox("ConsolidadoADO (pr_ListarConsolidado) --> " & myException.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function

    Public Function pr_ListarTickets(ByVal sFiltro As String) As DataTable
        Dim sql As String = "SELECT p.nroTicket,p.nroPeriodo,p.codAsiento,p.nroAsiento,p.codAsiento+'-'+p.nroAsiento AS Asiento,p.nroDocumento,p.fchIngreso,p.fchEmision,p.fchVuelo,p.monTipoCambio," & _
                            "m.sigMoneda,p.monTotal,e.nomEmpresa,p.nroConPago,flgDetraccion FROM dbo.V_Provision p INNER JOIN dbo.M_EmpresaTransporte e on p.codEmpresa=e.CodEmpresa INNER JOIN M_Moneda m ON p.CodMoneda=m.CodMoneda"
        Dim scriterio As String = IIf(sFiltro.Trim = "", "", " Where " & Trim(sFiltro))
        Dim dt As New DataTable
        Using cn As New SqlConnection(MiConexion.GetCnx)
            Try
                cn.Open()
                Using da As New SqlDataAdapter(sql & scriterio, cn)
                    da.Fill(dt)
                End Using
                cn.Close()
                Return dt
            Catch myException As Exception
                cn.Close()
                MsgBox("ConsolidadoADO (pr_ListarTickets) --> " & myException.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function

    Public Function pr_QuitarTicketManifiestoCabecera(ByVal sTicket As String) As Boolean
        Const sInstruccion As String = " Update dbo.C_Manifiesto Set NroTicket = @NroTicket, FlgProducto = @lProducto, EstadoManifiesto = @Estado WHERE Nroticket = @NroTicketB"
        Using cn As New SqlConnection(MiConexion.GetCnx)
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Try
                ' primero quito los numeros de ticket a los vuelos que estan consolidados
                Dim mySqlCommand As SqlCommand = New SqlCommand(sInstruccion, cn) With {.Transaction = oTran}
                mySqlCommand.Parameters.AddWithValue("@NroTicketB", sTicket)
                mySqlCommand.Parameters.AddWithValue("@NroTicket", DBNull.Value)
                mySqlCommand.Parameters.AddWithValue("@lProducto", False)
                mySqlCommand.Parameters.AddWithValue("@Estado", "2")
                mySqlCommand.ExecuteNonQuery()
                oTran.Commit()
                cn.Close()
                ' ahora elimino el ticket de provision
                cmd.Connection = cn
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandText = "pr_EliminarProvision"
                cmd.Parameters.Add(New SqlParameter("@psTicket", SqlDbType.Char))
                cmd.Parameters("@psTicket").Value = sTicket
                cn.Open()
                cmd.ExecuteNonQuery()
                cn.Close()
                Return True
            Catch myException As Exception
                oTran.Rollback()
                cn.Close()
                MsgBox("ConsolidadoADO (pr_QuitarTicketManifiestoCabecera) --> " & myException.Message, MsgBoxStyle.Critical)
                Return False
            End Try
        End Using
    End Function

    Public Function pr_TraerAnalisis(ByVal sEmpresa As String) As String
        Dim scodanl As String = "0000000"
        Dim sql As String = String.Format("select CodAnalisis from dbo.M_EmpresaTransporte where codempresa='{0}'", sEmpresa)
        Using cn As New SqlConnection(ConexionADO.GetCnxContabilidad)
            Try
                cn.Open()
                Using cmd As New SqlCommand(sql, cn)
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Using dt As DataTable = New DataTable("tasn")
                        da.Fill(dt)
                        Dim drCurrent As DataRow
                        For Each drCurrent In dt.Rows
                            scodanl = drCurrent("CodAnalisis")
                        Next
                    End Using
                End Using
                cn.Close()
                Return scodanl
            Catch ex As Exception
                cn.Close()
                MsgBox("ConsolidadoADO (pr_TraerAnalisis) --> " & ex.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function

    Public Function pr_TraerProvision(ByVal snroticket As String) As V_ProvisionBE
        Dim _rBE As New V_ProvisionBE
        Try
            Dim dtr As SqlDataReader
            cmd.Connection = cnx
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "pr_TraerProvision"
            cmd.Parameters.Add(New SqlParameter("@pNroTicket", SqlDbType.Char))
            cmd.Parameters("@pNroTicket").Value = snroticket
            cnx.Open()
            dtr = cmd.ExecuteReader
            If dtr.HasRows = True Then
                dtr.Read()
                With _rBE
                    .NroTicket = dtr("NroTicket")
                    .NroPeriodo = dtr("NroPeriodo")
                    .CodAsiento = dtr("CodAsiento")
                    .NroAsiento = dtr("NroAsiento")
                    .TipDocumento = dtr("TipDocumento")
                    .NroDocumento = dtr("NroDocumento")
                    If IsDBNull(dtr("FchIngreso")) Then
                        .FchIngreso = Date.Now
                    Else
                        .FchIngreso = dtr("FchIngreso")
                    End If
                    If IsDBNull(dtr("FchEmision")) Then
                        .FchEmision = Date.Now
                    Else
                        .FchEmision = dtr("FchEmision")
                    End If
                    If IsDBNull(dtr("FchVuelo")) Then
                        .FchVuelo = Date.Now
                    Else
                        .FchVuelo = dtr("FchVuelo")
                    End If
                    .MonTipoCambio = dtr("MonTipoCambio")
                    .CodMoneda = dtr("CodMoneda")
                    .MonTotal = dtr("MonTotal")
                    .CodEmpresa = dtr("CodEmpresa")
                    .NroConPago = dtr("NroConPago")
                    .FlgDetraccion = dtr("FlgDetraccion")
                End With
            End If
            cmd.Parameters.Clear()
        Catch x As SqlException
            MsgBox("ConsolidadoADO (pr_TraerProvision) --> " & x.Message, MsgBoxStyle.Critical)
        Finally
            If cnx.State = ConnectionState.Open Then
                cnx.Close()
            End If
        End Try
        Return _rBE
    End Function

    Public Function pr_VerificarCierreContable(ByVal sPeriodo As String) As Boolean
        Dim lflgCerrado As Boolean = False
        Using cn As New SqlConnection(ConexionADO.GetCnxContabilidad)
            Try
                cn.Open()
                Using cmd As New SqlCommand(String.Format("SELECT cm.flgcie,CAST(ISNULL(cc.flgcom,0) AS BIT) AS flgcom FROM dbo.cierre cm left join dbo.cierrecompras cc ON cm.anomes=cc.anomes WHERE (cm.anomes='{0}')", sPeriodo), cn)
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Using dt As DataTable = New DataTable("tCierre")
                        da.Fill(dt)
                        Dim drCurrent As DataRow
                        For Each drCurrent In dt.Rows
                            lflgCerrado = drCurrent("flgcie") Or drCurrent("flgcom")
                        Next
                    End Using
                End Using
                cn.Close()
                Return lflgCerrado
            Catch ex As Exception
                cn.Close()
                MsgBox("ConsolidadoADO (pr_VerificarCierreContable) --> " & ex.Message, MsgBoxStyle.Critical)
                Return False
            End Try
        End Using
    End Function
End Class